﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace BoAddonBiz_FreightForwarder.DataExport
{
    class B1Form_MPMonth : SAPBoAddon.B1Form
    {
        #region 继承基类
        protected override bool BeforeSaveCheckFormData()
        {
            return true;
        }

        protected override void FormEditModeChange(SAPBoAddon.frmItemEditMode frmEditMode)
        {
        }

        protected override void FormInfoSetting(SAPBoAddon.B1FormSetting FormSetting)
        {
            this.FormSetting.FormType = B1FormTypes.ado_AVA_FF_MPMonthForm;
            this.FormSetting.FormFileName = B1FormTypes.ado_AVA_FF_MPMonthForm + ".srf";
        }

        protected override void InitializeForm(SAPBoAddon.B1FormInitializePar InitPar)
        {
            int month = System.DateTime.Now.Month;
            string ItemUID_Selected = string.Format("cbo_{0}", month - 1);
            (this.CurrentForm.Items.Item(ItemUID_Selected).Specific as SAPbouiCOM.CheckBox).Checked = true;
            //this.CurrentForm.Items.Item(ItemUID_Selected).Click(SAPbouiCOM.BoCellClickType.ct_Regular);
            for (int mon = 12; mon >= DateTime.Now.Month; mon--)
            {
                this.CurrentForm.Items.Item(string.Format("cbo_{0}", mon)).Enabled = false;
            }

        }
        #endregion
        public B1Form_MPMonth()
        {
            this.frm_ItemEvent += new SAPBoAddon.IB1FormEvents.B1ItemEventEventHandler(B1Form_MPMonth_frm_ItemEvent);
        }

        void B1Form_MPMonth_frm_ItemEvent(SAPBoAddon.B1AddonBase.B1ItemEvent pVal, ref bool BubbleEvent)
        {
            if (pVal.Before_Action)
            {

            }
            else
            {
                if (pVal.ItemUID == "btn_openf" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    string FilePath = string.Empty;
                    FilePath = SAPBoAddon.B1Assistant.WinForm.WinForm_GetOpenFileName();
                    SAPBoAddon.B1Assistant.Item.SetValue(this.CurrentForm, "16", FilePath);
                }

                if (pVal.ItemUID == "btn_Exp" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    this.ExportSiemensMonthData();
                }
                if (pVal.ItemUID == "cbo_all" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    string isSelectedAll = this.CurrentForm.DataSources.UserDataSources.Item("monall").Value;
                    int selectList = (this.CurrentForm.Items.Item("cbo_last").Specific as SAPbouiCOM.CheckBox).Checked ? 13 : DateTime.Now.Month;
                    for (int i = 1; i < selectList; i++)
                    {
                        this.CurrentForm.DataSources.UserDataSources.Item(string.Format("mon{0}", i)).Value = isSelectedAll;
                    }
                }
                if (pVal.ItemUID == "cbo_last" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    bool isLastyear = (this.CurrentForm.Items.Item("cbo_last").Specific as SAPbouiCOM.CheckBox).Checked;
                    for (int mon = 12; mon >= DateTime.Now.Month; mon--)
                    {
                        (this.CurrentForm.Items.Item(string.Format("cbo_{0}", mon)).Specific as SAPbouiCOM.CheckBox).Checked = false;
                        this.CurrentForm.Items.Item(string.Format("cbo_{0}", mon)).Enabled = isLastyear;
                    }
                }
            }
        }
        #region 私有函数
        /// <summary>
        /// 处理字符（去掉特殊字符，字母大写）
        /// </summary>
        private string handleString(string strVal)
        {
            if (string.IsNullOrEmpty(strVal))
            {
                return strVal;
            }
            return strVal.ToUpper().Replace(",", string.Empty).Replace(".", string.Empty).Replace(" ", string.Empty);
        }

        private void ExportSiemensMonthData()
        {
            string fileName = SAPBoAddon.B1Assistant.Item.GetValue(this.CurrentForm, "16").ToString();
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                SBOApp.MessageBox("未安装Excel");
                return;
            }
            Excel.Workbook workbook = xlApp.Workbooks._Open(fileName);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    

            //Dictionary<string, int> dic_ProductBeginRow = new Dictionary<string, int> { { "Air In", -1 }, { "Air Out", -1 }, { "Sea In", -1 }, { "Sea Out", -1 } };
            Dictionary<string, int> dic_CardNameIndex = new Dictionary<string, int>();
            string product = string.Empty, Operateby = string.Empty, cardName = string.Empty, lastproduct = string.Empty;
            int rowCount = 200, row_Insert = 0;
            Dictionary<string, int> dic_ProductLastRow = new Dictionary<string, int> { { "Air In", -1 }, { "Air Out", -1 }, { "Sea In", -1 }, { "Sea Out", -1 } };
            SAPbobsCOM.Recordset Rst = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
            string CardFname = string.Empty;
            List<string> List_AllCardFname = new List<string>();
            List<string> List_ExcelCardName = new List<string>();

            Rst.DoQuery("SELECT CardCode,CardFName FROM [OCRD] WHERE GroupCode=102 ");
            Rst.MoveFirst();
            for (int i = 0; i < Rst.RecordCount; i++)
            {
                CardFname = Rst.Fields.Item("CardFName").Value;
                //List_AllCardFname.Add(handleString(CardFname)); 
                List_AllCardFname.Add(CardFname);

                Rst.MoveNext();
            }

            #region 添加新增的客户行
            for (int i = 1; i < rowCount; i++)
            {
                product = worksheet.get_Range(string.Format("A{0}", i)).Value;
                lastproduct = string.IsNullOrWhiteSpace(product) ? lastproduct : product;
                Operateby = worksheet.get_Range(string.Format("B{0}", i)).Value;
                cardName = worksheet.get_Range(string.Format("C{0}", i)).Value;

                if (Operateby != "MNP BJS" && !dic_ProductLastRow.ContainsKey(lastproduct)) continue;
                if (!string.IsNullOrEmpty(cardName) && !string.IsNullOrEmpty(Operateby))
                {
                    dic_ProductLastRow[lastproduct] = i;
                }

                if (Operateby == "MNP BJS")
                    List_ExcelCardName.Add(handleString(cardName));
                if (string.IsNullOrEmpty(cardName) && string.IsNullOrEmpty(Operateby))
                {
                    if (List_ExcelCardName.Count > 0)
                    {
                        foreach (var item_cardName in List_AllCardFname.Where(ItemnotinExcel => !List_ExcelCardName.Contains(handleString(ItemnotinExcel))))
                        {
                            row_Insert = dic_ProductLastRow[lastproduct];
                            worksheet.get_Range(string.Format("A{0}", row_Insert), string.Format("BW{0}", row_Insert)).EntireRow.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                            worksheet.Cells[2][row_Insert] = "MNP BJS";
                            worksheet.Cells[3][row_Insert] = item_cardName;
                            i++;
                        }
                        i--;
                        List_ExcelCardName.Clear();
                    }

                }
                //if (dic_ProductBeginRow["Sea Out"] > 0 && string.IsNullOrWhiteSpace(cardName))
                //{
                //    break;
                //}
            }
            fileName = fileName.Insert(fileName.LastIndexOf("."), "_" + DateTime.Now.ToString("yyyyMMdd-HHmmss"));
            workbook.SaveAs(fileName);
            workbook.Close(false, Type.Missing, Type.Missing);
            #endregion

            workbook = xlApp.Workbooks._Open(fileName);
            worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    

            #region 取4种业务所在的行索引
            for (int i = 1; i < rowCount; i++)
            {
                product = worksheet.get_Range(string.Format("A{0}", i)).Value;
                lastproduct = string.IsNullOrWhiteSpace(product) ? lastproduct : product;
                Operateby = worksheet.get_Range(string.Format("B{0}", i)).Value;
                cardName = worksheet.get_Range(string.Format("C{0}", i)).Value;
                //if (!string.IsNullOrEmpty(product))
                //{
                //    if (dic_ProductBeginRow.ContainsKey(product))
                //    {
                //        dic_ProductBeginRow[product] = i;
                //    }
                //}
                if (Operateby != "MNP BJS") continue;
                //dic_ProductLastRow[lastproduct] = i;
                dic_CardNameIndex.Add(string.Format("{0}-{1}", handleString(lastproduct), handleString(cardName)), i);
                //if (dic_ProductBeginRow["Sea Out"] > 0 && string.IsNullOrWhiteSpace(cardName))
                //{
                //    break;
                //}
            }
            #endregion
            string transportType = string.Empty, keyItem = string.Empty, SQL_GetData = string.Empty;
            int row = 0, startColumnIndex = 0;
            string[] monthEndDay = { "31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31" };
            int year = (this.CurrentForm.Items.Item("cbo_last").Specific as SAPbouiCOM.CheckBox).Checked ? DateTime.Now.Year - 1 : DateTime.Now.Year;
            #region 导出数据行
            for (int month = 1; month < 13; month++)
            {
                if (this.CurrentForm.DataSources.UserDataSources.Item(string.Format("mon{0}", month)).Value.Trim() == "Y")
                {
                    SBOApp.StatusBar.SetText(string.Format("正在导出 {0}月数据...", month), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
                    startColumnIndex = month <= 6 ? 5 * month - 1 : 5 * month + 4;

                    SQL_GetData = string.Format("  EXEC AVA_MonthofSieMens N'{0}-{1}-1',N'{0}-{1}-{2}' ", year, month, monthEndDay[month - 1]);
                    Rst.DoQuery(SQL_GetData);

                    Rst.MoveFirst();
                    for (int i = 0; i < Rst.RecordCount; i++)
                    {
                        transportType = Rst.Fields.Item("Transtype").Value;
                        cardName = Rst.Fields.Item("CardFName").Value;
                        keyItem = string.Format("{0}-{1}", handleString(transportType), handleString(cardName));
                        if (dic_CardNameIndex.ContainsKey(keyItem))
                        {
                            row = dic_CardNameIndex[keyItem];

                            worksheet.Cells[startColumnIndex][row] = Rst.Fields.Item("shpts").Value;
                            worksheet.Cells[startColumnIndex + 1][row] = Rst.Fields.Item("pcs").Value;
                            worksheet.Cells[startColumnIndex + 2][row] = Rst.Fields.Item("GW").Value;
                            worksheet.Cells[startColumnIndex + 3][row] = Rst.Fields.Item("CW").Value;
                            worksheet.Cells[startColumnIndex + 4][row] = Rst.Fields.Item("Revenue").Value;
                        }
                        else
                        {
                            throw new Exception("客户列表异常.");
                        }

                        Rst.MoveNext();
                    }
                }

            }
            #endregion

            //workbook.SaveAs(fileName.Insert(fileName.LastIndexOf("."), "_" + DateTime.Now.ToString("yyyyMMdd-HHmmss")));
            workbook.Save();
            workbook.Close(false, Type.Missing, Type.Missing);

            worksheet = null;
            workbook = null;
            xlApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
            xlApp = null;
            GC.Collect();
            SBOApp.StatusBar.SetText("导出完成.", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);

        }

        public string ConvertToColName(int colIndex)
        {
            string strRtn = "";
            if (colIndex <= 0)
                return "";
            int numMod = 0;
            int numTemp = colIndex;
            char ch = 'A';
            do
            {
                numMod = numTemp % 26;
                numTemp = numTemp / 26;
                if (numMod == 0)
                {
                    ch = 'Z';
                    numTemp--;
                }
                else
                    ch = (char)('A' + numMod - 1);
                strRtn = ch + strRtn;
            }
            while (numTemp > 0);
            return strRtn;

        }
        #endregion

    }
}
